<?php
class ModelCatalogProduct extends Model {

				public function getChecksum() {
					$query = $this->db->query("CHECKSUM TABLE " . DB_PREFIX . "product, "
						. DB_PREFIX . "category,"
						. DB_PREFIX . "product_to_category,"
						. DB_PREFIX . "product_description"

					);
					return $query->rows;
				}

				public function getTotalProductsByManufacturerId($manufacturer_id) {
					$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE manufacturer_id = '" . (int)$manufacturer_id . "'");
					return $query->row['total'];
				}

                public function getProductsByOrder($order_id){
                    $ordercheck=M('order');
                    $flag=0;

                    $flag=$ordercheck
                    ->where('customer_id='.$this->customer->getId().' and order_id='.$order_id)
                    ->find();

                    if($flag){
                        $productdb= M('order_product');
                        $data=$productdb
                        ->alias('o')
                        ->join('LEFT JOIN product p ON p.product_id=o.product_id')
                        // ->join('product_description d ON d.product_id=o.product_id')
                        ->where('o.order_id='.$order_id)
                        ->field('p.image,o.product_id,o.name')
                        ->select();
                        if($data){
                            foreach( $data as $key){
                                if($key['image']){
                                    $key['image'] = HTTP_SERVER . 'image/' . $key['image'];

                                }else{
                                    $key['image'] = null;
                                }
                                $data2[]=$key;
                            }
                        }
                        return $data2;
                    }else{
                        return 0;
                    }
                }
                public function getProductImageByProductId($product_id){

                        $productdb= M('product');
                        $data=$productdb
                        ->where('product_id='.$product_id)
                        ->field('image')
                        ->find();

                        if($data){
                            $data['image'] =  HTTP_SERVER . 'image/' . $data['image'];
                            return $data;
                        }else{
                            return null;
                        }


                }

           public function getProductsData($data = array(), $customer) {
                if ($customer->isLogged()) {
                    $customer_group_id = $customer->getGroupId();
                } else {
                    $customer_group_id = $this->config->get('config_customer_group_id');
                }

                //$sql = "SELECT p.product_id";
                $sql = "SELECT p.product_id, p.minimum, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating,
                   (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";


                if (!empty($data['filter_category_id'])) {
                    if (!empty($data['filter_sub_category'])) {
                        $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
                    } else {
                        $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
                    }
                    if (!empty($data['filter_filter'])) {
                        $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
                    } else {
                        $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
                    }
                } else {
                    $sql .= " FROM " . DB_PREFIX . "product p";
                }

                if(!empty($data['filter_vendor_id'])){
                    $sql .= " LEFT JOIN ". DB_PREFIX . "vendor ve ON (p.product_id = ve.vproduct_id)"." ";
                }

                $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

                if (!empty($data['filter_category_id'])) {
                    if (!empty($data['filter_sub_category'])) {
                        $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
                    } else {
                        $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
                    }

                    if (!empty($data['filter_filter'])) {
                        $implode = array();

                        $filters = explode(',', $data['filter_filter']);

                        foreach ($filters as $filter_id) {
                            $implode[] = (int)$filter_id;
                        }

                        $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
                    }
                }

                if (!empty($data['filter_name']) ) {
                    $sql .= " AND (";

                    if (!empty($data['filter_name'])) {
                        $implode = array();

                        $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

                        foreach ($words as $word) {
                            $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                        }

                        if ($implode) {
                            $sql .= " " . implode(" AND ", $implode) . "";
                        }

                        if (!empty($data['filter_description'])) {
                            $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                        }
                    }

                    if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                        $sql .= " OR ";
                    }

                    if (!empty($data['filter_tag'])) {
                        $sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
                    }

                    if (!empty($data['filter_name'])) {
                        $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                        $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                        $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                        $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                        $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                        $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                    }

					//商品编码
					if (!empty($data['filter_product_code'])) {
                        $sql .= "OR p.product_code LIKE '%" . $this->db->escape($data['filter_product_code']) . "%'";
                    }

					//条形码
					if (!empty($data['filter_sku'])) {
                        $sql .= "OR p.sku LIKE '%" . $this->db->escape($data['filter_sku']) . "%'";
                    }

                    $sql .= ")";
                }
                if (($customer->getIsAll())==0) {
                    $sql .= " AND p.is_part = 0 ";
                }

                if (!empty($data['filter_manufacturer_id'])) {
                    $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
                }

                if (!empty($data['filter_vendor_id'])) {
                    $sql .= " AND ve.vendor = '" . (int)$data['filter_vendor_id'] . "'";
                }

                $sql .= " GROUP BY p.product_id";

                $sort_data = array(
                    'minimum'   =>'p.minimum',
                    'name'      =>'pd.name',
                    'model'     =>'p.model',
                    'quantity'  =>'p.quantity',
                    'price'     =>'p.price',
                    'rating'    =>'rating',
                    'sort_order'=>'p.sort_order',
                    'date_added'=>'p.date_added',
                    'sold'      =>'p.sold'
                );

                $sortSql = "";
                if (isset($data['sort']) && in_array($data['sort'], array_keys($sort_data))) {
                    if ($data['sort'] == 'name' || $data['sort'] == 'model') {
                        $sortSql .= " ORDER BY LCASE(" . $sort_data[$data['sort']] . ")";
                    } elseif ($data['sort'] == 'price') {
                        $sortSql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
                    } else {
                        $sortSql .= " ORDER BY " . $sort_data[$data['sort']];
                    }
                } else {
                    $sortSql .= " ORDER BY p.sort_order";
                }

                if (isset($data['order']) && (strtolower($data['order']) == strtolower('ASC'))) {
                    $sortSql .= " ASC, LCASE(pd.name) ASC";
                } else {
                    $sortSql .= " DESC, LCASE(pd.name) DESC";
                }

                $sql.= $sortSql;

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['limit'] < 1) {
                        $limit = 20;
                    }else {
                        $limit = (int)$data['limit'];
                    }

                    $offset = 0;
                    if ($data['start'] < 0) {
                        $offset = 0;
                    }else{
                        $offset = (int)$data['start'];
                    }

                    $sql .= " LIMIT " . $offset . "," . $limit;
                }

                $product_data = array();

                $query = $this->db->query($sql);

                foreach ($query->rows as $result) {
                    $product_data[$result['product_id']] = $result['product_id'];
                }

                $product_ids = array_keys($product_data);
                // $logcenter_disabled_product_ids = $this->getLogcenterDisabledProductIds($customer);
                // $product_ids = array_diff($product_ids, $logcenter_disabled_product_ids);

                return $this->getProductsByIds($product_ids, $customer, $sortSql);
            }

            public function getProductsByIds($product_ids, $customer, $sortSql = "ORDER BY p.product_id ASC") {

                if(count($product_ids) == 0){
                    return false;
                }
                //Liqn 禁用物流营销中心不能发货的产品ids
                $logcenter_disabled_product_ids = $this->getLogcenterDisabledProductIds($customer);
                $product_ids = array_diff($product_ids, $logcenter_disabled_product_ids);

                if ($customer->isLogged()) {
                    $customer_group_id = $customer->getGroupId();
                } else {
                    $customer_group_id = $this->config->get('config_customer_group_id');
                }

                $query = $this->db->query("SELECT DISTINCT *, p.lack_status,p.lack_reason,p.packing_no, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
(SELECT product_special_id FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special_id, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$customer_group_id . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id IN (" . implode(',', $product_ids) . ") AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'".$sortSql);

                $product_data = array();
                if ($query->num_rows) {
                    foreach ($query->rows as $result) {
						//检查区域
						if($this->checkArea($result['product_id']) == 0){
							continue;
						}

						$specialIds[$result['product_id']] = $result['special_id'];

						$days = (int)((time()-strtotime($result['date_added']))/(24*3600));
						if(($days < 60 || $result['newpro'] == 1) && $result['show_new'] == 1){
							$show_new = 1;
						}
						else{
							$show_new = 0;
						}

                         //清仓商品获取后台库存
                        // if ($result['clearance']||$result['lack_status']==3) {
                             $sql = "SELECT SUM(i.available_quantity) as 'clearqty',pv.product_code FROM inventory AS i LEFT JOIN product_option_value AS pv ON pv.product_code = i.product_code LEFT JOIN warehouse AS w ON i.warehouse_id = w.warehouse_id WHERE i.`status` = 1 AND w.type!=3 AND pv.product_id=  ".$result['product_id'];
                                    $query = $this->db->query($sql);
                                    $clearqty = $query->row['clearqty'];

                                    $procode = $pCode = substr($query->row['product_code'], 0, 10);
                                    $tmp = $this->getsaleqty($procode,1);
                                    $saleqty=0;
                                    foreach ($tmp as $wh=>$qty) {
                                        $saleqty += $qty;
                                    }
                                $clearqty = $clearqty-$saleqty;
                             $clearqty = max(0,$clearqty);
                        // }

                        $product_data[$result['product_id']] = array(
                                        'product_id'       => $result['product_id'],
                                        'name'             => $result['name'],
                                        'description'      => $result['description'],
                                        'meta_description' => $result['meta_description'],
                                        'meta_keyword'     => $result['meta_keyword'],
                                        'meta_title'     => $result['meta_title'],
                                        'tag'              => $result['tag'],
                                        'model'            => $result['model'],
										'product_code'		 => $result['product_code'],
                                        'sku'              => $result['sku'],
                                        'upc'              => $result['upc'],
                                        'ean'              => $result['ean'],
                                        'jan'              => $result['jan'],
                                        'isbn'             => $result['isbn'],
                                        'mpn'              => $result['mpn'],
                                        'location'         => $result['location'],
                                        'quantity'         => $result['quantity'],
                                        'stock_status'     => $result['stock_status'],
                                        'image'            => $result['image'],
                                        'manufacturer_id'  => $result['manufacturer_id'],
                                        'manufacturer'     => $result['manufacturer'],
                                        'price'            => ($result['discount'] ? $result['discount'] : $result['price']),
                                        'sale_price'            => ($result['discount'] ? $result['discount'] : $result['sale_price']),
                                        'special'          => isset($result['special']) ? $result['special'] : '',
                                        'reward'           => $result['reward'],
                                        'points'           => $result['points'],
                                        'tax_class_id'     => $result['tax_class_id'],
                                        'date_available'   => $result['date_available'],
                                        'weight'           => $result['weight'],
                                        'weight_class_id'  => $result['weight_class_id'],
                                        'length'           => $result['length'],
                                        'width'            => $result['width'],
                                        'height'           => $result['height'],
                                        'length_class_id'  => $result['length_class_id'],
                                        'subtract'         => $result['subtract'],
                                        'rating'           => round($result['rating']),
                                        'reviews'          => $result['reviews'] ? $result['reviews'] : 0,
                                        'minimum'          => $result['minimum'],
                                        'addnum'           => $result['addnum'],
                                        'sort_order'       => $result['sort_order'],
                                        'status'           => $result['status'],
                                        'packing_no'           => $result['packing_no'],
                                        'date_added'       => $result['date_added'],
                                        'date_modified'    => $result['date_modified'],
                                        'viewed'           => $result['viewed'],
										'weight_class'     => $result['weight_class'],
										'length_class'     => $result['length_class'],
										'product_type' => $result['product_type'],
									    'show_new' => $show_new,
                                        'show_clearance' => $product['show_clearance'],//是否显示“折”
										'recommend' => $result['recommend'],
                                        'clearance' => $result['clearance'],
                                        'lack_status' => $result['lack_status'],
										'lack_reason' => $result['lack_reason'],
										'promotion' => $result['promotion'],
                                        'is_single' => $result['is_single'],
										'clearqty' => $clearqty,
                                    );
                    }
                    // var_dump($product_data);
		$specialIds = array_filter($specialIds, function($item){
		    return !empty($item);
		});
		if(count($specialIds) > 0){
		    $specialsQuery = $this->db->query("SELECT date_start, date_end, price, product_id FROM " . DB_PREFIX . "product_special ps WHERE ps.customer_group_id = '" . (int)$customer_group_id."' AND ps.product_special_id IN (" . implode(',', $specialIds) . ")");

		    foreach ($specialsQuery->rows as $special) {

			if(isset($product_data[$special['product_id']])){
			    $product_data[$special['product_id']]['special'] = $special['price'];
			    $product_data[$special['product_id']]['special_start_date'] = $special['date_start'];
			    $product_data[$special['product_id']]['special_end_date'] = $special['date_end'];
			}
		    }
		}
                    return $product_data;
                } else {
                    return false;
                }
            }
            public function getOrderStatusByName($status) {

                $query = $this->db->query("SELECT order_status_id FROM " . DB_PREFIX . "order_status WHERE LCASE(name) = '" . $this->db->escape(utf8_strtolower($status)) . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

                return $query->rows;
            }
     public function getsaleqty($productCode,$flog) {
        $ret = '';
        $date = date('Ymd', strtotime('-2 month'));
        //获取单品销售的未发货数据
        $sql = "
        SELECT
            SUM(OP.`lack_quantity`) AS qty,OP.`product_code`,
            L.`default_warehouse` AS wh_id
        FROM
            `order_product` AS OP
            LEFT JOIN `order` AS O ON (O.`order_id`=OP.`order_id`)
            LEFT JOIN `logcenters` AS L ON (O.`logcenter_id`=L.`logcenter_id`)
        WHERE
            (O.order_status_id = '1' OR O.order_status_id = '2' OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '17') AND O.verify_status != '2' AND O.date_added > '".$date."000000'
            AND OP.product_code ='".$productCode."0'
        GROUP BY
            L.`default_warehouse`
        ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $qtyInWarehouse[$row['wh_id']] += max(0, $row['qty']);
        }
        //获取商品组内部商品的未发货数据
        $sql = "
        SELECT
            SUM(OPG.`lack_quantity`) AS qty,OPG.`product_code`,
            L.`default_warehouse` AS wh_id
        FROM
            `order_product_group` AS OPG
            LEFT JOIN `order_product` AS OP ON (OPG.`order_product_id`=OP.`order_product_id`)
            LEFT JOIN `order` AS O ON (O.`order_id`=OP.`order_id`)
            LEFT JOIN `logcenters` AS L ON (O.`logcenter_id`=L.`logcenter_id`)
        WHERE
            (O.order_status_id = '1' OR O.order_status_id = '2' OR O.order_status_id = '3' OR O.order_status_id = '20' OR O.order_status_id = '17') AND O.verify_status != '2' AND O.date_added > '".$date."000000'
            AND OPG.product_code ='".$productCode."0'
        GROUP BY
            L.`default_warehouse`
        ";
        // echo $sql;
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $qtyInWarehouse[$row['wh_id']] += max(0, $row['qty']);
        }
        foreach ($qtyInWarehouse as $wh_id=>$qty) {
            if ($qty > 0 && $wh_id > 0) {
                if (4 == $wh_id) {
                    $tmp['无锡'] += $qty;
                }else{
                    $tmp['永康'] += $qty;
                }
            }
        }
        krsort($tmp);
        foreach ($tmp as $wh=>$qty) {
            $ret .= $qty.'（'.$wh.'）<br />';
        }
        if ($flog==1) {
            return $tmp;

        }else{
            return substr($ret,0, -6);
        }

    }

        public function getWeightClasses($data = array()) {
            if ($data) {
                $sql = "SELECT * FROM " . DB_PREFIX . "weight_class wc LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (wc.weight_class_id = wcd.weight_class_id) WHERE wcd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

                $sort_data = array(
                    'title',
                    'unit',
                    'value'
                );

                if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
                    $sql .= " ORDER BY " . $data['sort'];
                } else {
                    $sql .= " ORDER BY title";
                }

                if (isset($data['order']) && ($data['order'] == 'DESC')) {
                    $sql .= " DESC";
                } else {
                    $sql .= " ASC";
                }

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['start'] < 0) {
                        $data['start'] = 0;
                    }

                    if ($data['limit'] < 1) {
                        $data['limit'] = 20;
                    }

                    $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
                }

                $query = $this->db->query($sql);

                return $query->rows;
            } else {
                $weight_class_data = $this->cache->get('weight_class.' . (int)$this->config->get('config_language_id'));

                if (!$weight_class_data) {
                    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "weight_class wc LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (wc.weight_class_id = wcd.weight_class_id) WHERE wcd.language_id = '" . (int)$this->config->get('config_language_id') . "'");

                    $weight_class_data = $query->rows;

                    $this->cache->set('weight_class.' . (int)$this->config->get('config_language_id'), $weight_class_data);
                }

                return $weight_class_data;
            }
        }

        public function getStockStatuses($data = array()) {
            if ($data) {
                $sql = "SELECT * FROM " . DB_PREFIX . "stock_status WHERE language_id = '" . (int)$this->config->get('config_language_id') . "'";

                $sql .= " ORDER BY name";

                if (isset($data['order']) && ($data['order'] == 'DESC')) {
                    $sql .= " DESC";
                } else {
                    $sql .= " ASC";
                }

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['start'] < 0) {
                        $data['start'] = 0;
                    }

                    if ($data['limit'] < 1) {
                        $data['limit'] = 20;
                    }

                    $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
                }

                $query = $this->db->query($sql);

                return $query->rows;
            } else {
                $stock_status_data = $this->cache->get('stock_status.' . (int)$this->config->get('config_language_id'));

                if (!$stock_status_data) {
                    $query = $this->db->query("SELECT stock_status_id, name FROM " . DB_PREFIX . "stock_status WHERE language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY name");

                    $stock_status_data = $query->rows;

                    $this->cache->set('stock_status.' . (int)$this->config->get('config_language_id'), $stock_status_data);
                }

                return $stock_status_data;
            }
        }

        public function getLengthClasses($data = array()) {
            if ($data) {
                $sql = "SELECT * FROM " . DB_PREFIX . "length_class lc LEFT JOIN " . DB_PREFIX . "length_class_description lcd ON (lc.length_class_id = lcd.length_class_id) WHERE lcd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

                $sort_data = array(
                    'title',
                    'unit',
                    'value'
                );

                if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
                    $sql .= " ORDER BY " . $data['sort'];
                } else {
                    $sql .= " ORDER BY title";
                }

                if (isset($data['order']) && ($data['order'] == 'DESC')) {
                    $sql .= " DESC";
                } else {
                    $sql .= " ASC";
                }

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['start'] < 0) {
                        $data['start'] = 0;
                    }

                    if ($data['limit'] < 1) {
                        $data['limit'] = 20;
                    }

                    $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
                }

                $query = $this->db->query($sql);

                return $query->rows;
            } else {
                $length_class_data = $this->cache->get('length_class.' . (int)$this->config->get('config_language_id'));

                if (!$length_class_data) {
                    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "length_class lc LEFT JOIN " . DB_PREFIX . "length_class_description lcd ON (lc.length_class_id = lcd.length_class_id) WHERE lcd.language_id = '" . (int)$this->config->get('config_language_id') . "'");

                    $length_class_data = $query->rows;

                    $this->cache->set('length_class.' . (int)$this->config->get('config_language_id'), $length_class_data);
                }

                return $length_class_data;
            }
        }

        public function getStores($data = array()) {
            $store_data = $this->cache->get('store');

            if (!$store_data) {
                $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "store ORDER BY url");

                $store_data = $query->rows;

                $this->cache->set('store', $store_data);
            }

            return $store_data;
        }

            public function getRecurrings($data = array()) {
                $sql = "SELECT * FROM `" . DB_PREFIX . "recurring` r LEFT JOIN " . DB_PREFIX . "recurring_description rd ON (r.recurring_id = rd.recurring_id) WHERE rd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

                if (!empty($data['filter_name'])) {
                    $sql .= " AND rd.name LIKE '" . $this->db->escape($data['filter_name']) . "%'";
                }

                $sort_data = array(
                    'rd.name',
                    'r.sort_order'
                );

                if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
                    $sql .= " ORDER BY " . $data['sort'];
                } else {
                    $sql .= " ORDER BY rd.name";
                }

                if (isset($data['order']) && ($data['order'] == 'DESC')) {
                    $sql .= " DESC";
                } else {
                    $sql .= " ASC";
                }

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['start'] < 0) {
                        $data['start'] = 0;
                    }

                    if ($data['limit'] < 1) {
                        $data['limit'] = 20;
                    }

                    $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
                }

                $query = $this->db->query($sql);

                return $query->rows;
            }


            public function checkProductExists($product_id) {
                $query = $this->db->query("SELECT COUNT(DISTINCT product_id) AS total FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");

                if (isset($query->row['total'])) {
                    return $query->row['total'];
                } else {
                    return 0;
                }
            }

            public static $fields = array(
                "category" => "p2c.category_id",
                "quantity" => "p.quantity",
                "stock_status" => "p.stock_status_id",
                "manufacturer" => "p.manufacturer_id",
                "model" => "p.model",
                "upc" => "p.upc",
                "name" => "pd.name",
                "date_start" => "ps.date_start",
                "date_end" => "ps.date_end",
                "product_id" => "p.product_id",
                "price" => "p.price",
                "sale_price" => "p.sale_price",
                "status" => "p.status",
                "date_available" => "p.date_available",
                "store_id" => "p2s.store_id",
                "filter_text" => "filter_text",
            );


            public static $operands = array(
                "=" => "=",
                "!=" => "!=",
                ">" => ">",
                ">=" => ">=",
                "<" => "<",
                "<=" => "<=",
                "!<" => "!<",
                "!>" => "!>",
                "<>" => "<>",
                "in" => "in",
                "not_in" => "not in",
                "like" => "like",
            );

            public static $logicalOperands = array(
                "and"=>"AND",
                "or"=>"OR"
            );

            public static $sort_data = array(
                            'name'=>'pd.name',
                            'model'=>'p.model',
                            'quantity'=>'p.quantity',
                            'price'=>'p.price',
                            'rating'=>'rating',
                            'sort_order'=>'p.sort_order',
                            'date_added'=>'p.date_added'
                        );


            private function searchHelper($request){
                $sql = "";

                foreach($request['filters'] as $filter){
                    if(array_key_exists($filter['field'], static::$fields)) {

                        $operand = array_key_exists($filter['operand'], static::$operands) ? strtolower(static::$operands[$filter['operand']]) : "=";
                        $logicalOperand = isset($filter['logical_operand']) && array_key_exists(strtolower($filter['logical_operand']), static::$logicalOperands) ? $filter['logical_operand'] : "AND";

                        if (static::$fields[$filter['field']] == 'filter_text' ) {
                            $sql .= $logicalOperand."  (";

                            if (!empty( $filter['value'])) {
                                $implode = array();

                                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ',  $filter['value'])));

                                foreach ($words as $word) {
                                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                                }

                                if ($implode) {
                                    $sql .= " " . implode(" AND ", $implode) . "";
                                }

                                $sql .= " OR pd.description LIKE '%" . $this->db->escape( $filter['value']) . "%'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            if (!empty( $filter['value'])) {
                                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower( $filter['value'])) . "'";
                            }

                            $sql .= ")";
                        } else {
                            if($operand == "in" || $operand == "not in"){
                                $sql.=" ".$logicalOperand." ".static::$fields[$filter['field']]
                                    ." ".$operand." (".$this->db->escape(implode(",",$filter['value'])).")";
                            } elseif($operand == "like" ){
                                $sql.=" ".$logicalOperand." ".static::$fields[$filter['field']];
                                $sql .= " LIKE '%" . $this->db->escape( strtolower($filter['value'])) . "%'";
                            } else{
                                $sql.=" ".$logicalOperand." ".static::$fields[$filter['field']]
                                    ." ".$operand." ".$this->db->escape($filter['value']);
                            }
                        }
                    }
                }

                return $sql;

            }


            public function search($data = array(), $request, $customer) {
                if ($customer->isLogged()) {
                    $customer_group_id = $customer->getGroupId();
                } else {
                    $customer_group_id = $this->config->get('config_customer_group_id');
                }

                $sql = "SELECT p.product_id
                                FROM " . DB_PREFIX . "product p
                              LEFT JOIN " . DB_PREFIX . "product_to_category p2c
                                        ON (p.product_id = p2c.product_id)
                              LEFT JOIN " . DB_PREFIX . "product_description pd
                                        ON (p.product_id = pd.product_id)
                              LEFT JOIN " . DB_PREFIX . "product_to_store p2s
                                        ON (p.product_id = p2s.product_id)
                              WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
                              AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
                              ";

                        $sql.= $this->searchHelper($request);

                        $sql .= " GROUP BY p.product_id";

                $sortSql = "";
                if (($customer->getIsAll())==0) {
                    $sortSql .= " AND p.is_part = 0 ";
                }

                if (isset($request['sort']) && in_array($request['sort'], array_keys(static::$sort_data))) {
                    if ($request['sort'] == 'name' || $request['sort'] == 'model') {
                        $sortSql .= " ORDER BY LCASE(" . static::$sort_data[$request['sort']] . ")";
                    } elseif ($request['sort'] == 'price') {
                        $sortSql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
                    } else {
                        $sortSql .= " ORDER BY " . static::$sort_data[$request['sort']];
                    }
                } else {
                    $sortSql .= " ORDER BY p.sort_order";
                }

                if (isset($request['order']) && (strtolower($request['order']) == strtolower('DESC'))) {
                    $sortSql .= " DESC, LCASE(pd.name) DESC";
                } else {
                    $sortSql .= " ASC, LCASE(pd.name) ASC";
                }

                $sql.= $sortSql;

                if (isset($data['start']) || isset($data['limit'])) {
                    if ($data['limit'] < 1) {
                        $limit = 20;
                    }else {
                        $limit = (int)$data['limit'];
                    }

                    if ($data['start'] < 0) {
                        $offset = 0;
                    }else{
                        $offset = (int)$data['start'];
                    }

                    $sql .= " LIMIT " . $offset . "," . $limit;
                }

                if(isset($request['debug']) && isset($request['debug']) == true){
                    var_dump($sql);
                }

                $product_data = array();

                $query = $this->db->query($sql);
                foreach ($query->rows as $result) {
                    $product_data[$result['product_id']] = $result['product_id'];
                }

                return $this->getProductsByIds(array_keys($product_data), $customer, $sortSql);
            }

            public function getStore($store_id) {
                $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE store_id = '" . (int)$store_id . "'");
                return $query->rows;
            }

	public function getModulesByCode($code) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "module` WHERE `code` = '" . $this->db->escape($code) . "' ORDER BY `name`");

		return $query->rows;
	}

	public function getBanners($data = array()) {
		$sql = "SELECT * FROM " . DB_PREFIX . "banner";

		$sort_data = array(
			'name',
			'status'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY name";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}


	public function updateViewed($product_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'");
	}

	public function getProduct($product_id) {
		$query = $this->db->query("SELECT DISTINCT *,p.packing_no,p.lack_status,p.lack_reason, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "') AS reward, (SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, (SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, (SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

		if ($query->num_rows) {
			return array(
				'product_id'       => $query->row['product_id'],
				'name'             => $query->row['name'],
				'description'      => $query->row['description'],
				'meta_title'       => $query->row['meta_title'],
				'meta_description' => $query->row['meta_description'],
				'meta_keyword'     => $query->row['meta_keyword'],
				'tag'              => $query->row['tag'],
				'model'            => $query->row['model'],
				'sku'              => $query->row['sku'],
				'upc'              => $query->row['upc'],
				'ean'              => $query->row['ean'],
				'jan'              => $query->row['jan'],
				'isbn'             => $query->row['isbn'],
				'mpn'              => $query->row['mpn'],
				'location'         => $query->row['location'],
				'quantity'         => $query->row['quantity'],
				'stock_status'     => $query->row['stock_status'],
				'image'            => $query->row['image'],
				'manufacturer_id'  => $query->row['manufacturer_id'],
				'manufacturer'     => $query->row['manufacturer'],
                'price'            => ($query->row['discount'] ? $query->row['discount'] : $query->row['price']),
				'sale_price'            => ($query->row['discount'] ? $query->row['discount'] : $query->row['sale_price']),
				'special'          => $query->row['special'],
				'reward'           => $query->row['reward'],
				'points'           => $query->row['points'],
				'tax_class_id'     => $query->row['tax_class_id'],
				'date_available'   => $query->row['date_available'],
				'weight'           => $query->row['weight'],
				'weight_class_id'  => $query->row['weight_class_id'],
				'length'           => $query->row['length'],
				'width'            => $query->row['width'],
				'height'           => $query->row['height'],
				'length_class_id'  => $query->row['length_class_id'],
				'subtract'         => $query->row['subtract'],
				'rating'           => round($query->row['rating']),
				'reviews'          => $query->row['reviews'] ? $query->row['reviews'] : 0,
				'minimum'          => $query->row['minimum'],
                'addnum'           => $query->row['addnum'],
				'sort_order'       => $query->row['sort_order'],
				'status'           => $query->row['status'],
				'date_added'       => $query->row['date_added'],
				'date_modified'    => $query->row['date_modified'],
				'viewed'           => $query->row['viewed'],
                'packing_no'           => $query->row['packing_no']
			);
		} else {
			return false;
		}
	}

	public function getProducts($data = array()) {
		$sql = "SELECT p.packing_no,p.product_id,p.sale_price, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";

		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
			} else {
				$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
			}

			if (!empty($data['filter_filter'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
			} else {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
			}
		} else {
			$sql .= " FROM " . DB_PREFIX . "product p";
		}

		$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
			} else {
				$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
			}

			if (!empty($data['filter_filter'])) {
				$implode = array();

				$filters = explode(',', $data['filter_filter']);

				foreach ($filters as $filter_id) {
					$implode[] = (int)$filter_id;
				}

				$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
			}
		}

		if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
			$sql .= " AND (";

			if (!empty($data['filter_name'])) {
				$implode = array();

				$words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

				foreach ($words as $word) {
					$implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
				}

				if ($implode) {
					$sql .= " " . implode(" AND ", $implode) . "";
				}

				if (!empty($data['filter_description'])) {
					$sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
				}
			}

			if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
				$sql .= " OR ";
			}

			if (!empty($data['filter_tag'])) {
				$sql .= "pd.tag LIKE '%" . $this->db->escape($data['filter_tag']) . "%'";
			}

			if (!empty($data['filter_name'])) {
				$sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
			}

			$sql .= ")";
		}

		if (!empty($data['filter_manufacturer_id'])) {
			$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
		}

		$sql .= " GROUP BY p.product_id";

		$sort_data = array(
			'pd.name',
			'p.model',
			'p.quantity',
			'p.price',
			'rating',
			'p.sort_order',
			'p.date_added'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} elseif ($data['sort'] == 'p.price') {
				$sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC, LCASE(pd.name) DESC";
		} else {
			$sql .= " ASC, LCASE(pd.name) ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$product_data = array();

		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

		return $product_data;
	}

	public function getProductSpecials($data = array()) {
		$sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE())) GROUP BY ps.product_id";

		$sort_data = array(
			'pd.name',
			'p.model',
			'ps.price',
			'rating',
			'p.sort_order'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY p.sort_order";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC, LCASE(pd.name) DESC";
		} else {
			$sql .= " ASC, LCASE(pd.name) ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$product_data = array();

		$query = $this->db->query($sql);

		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

		return $product_data;
	}

	public function getLatestProducts($limit) {
		$product_data = $this->cache->get('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

		if (!$product_data) {
			$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);

			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}

			$this->cache->set('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit, $product_data);
		}

		return $product_data;
	}

	public function getPopularProducts($limit) {
		$product_data = array();

		$query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.viewed DESC, p.date_added DESC LIMIT " . (int)$limit);

		foreach ($query->rows as $result) {
			$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
		}

		return $product_data;
	}

	public function getBestSellerProducts($limit) {
		$product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

		if (!$product_data) {
			$product_data = array();

			$query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);

			foreach ($query->rows as $result) {
				$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
			}

			$this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit, $product_data);
		}

		return $product_data;
	}

	public function getProductAttributes($product_id) {
		$product_attribute_group_data = array();

		$product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . (int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");

		foreach ($product_attribute_group_query->rows as $product_attribute_group) {
			$product_attribute_data = array();

			$product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . (int)$product_id . "' AND a.attribute_group_id = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");

			foreach ($product_attribute_query->rows as $product_attribute) {
				$product_attribute_data[] = array(
					'attribute_id' => $product_attribute['attribute_id'],
					'name'         => $product_attribute['name'],
					'text'         => $product_attribute['text']
				);
			}

			$product_attribute_group_data[] = array(
				'attribute_group_id' => $product_attribute_group['attribute_group_id'],
				'name'               => $product_attribute_group['name'],
				'attribute'          => $product_attribute_data
			);
		}

		return $product_attribute_group_data;
	}

	public function getProductOptions($product_id) {
		$product_option_data = array();

		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");

		foreach ($product_option_query->rows as $product_option) {
			$product_option_value_data = array();

			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");


			foreach ($product_option_value_query->rows as $product_option_value) {
				$product_option_value_data[] = array(
					'product_option_value_id' => $product_option_value['product_option_value_id'],
					'option_value_id'         => $product_option_value['option_value_id'],
					'name'                    => $product_option_value['name'],
					'product_code'        => $product_option_value['product_code'],
					'image'                   => $product_option_value['image'],
					'quantity'                => $product_option_value['quantity'],
					'subtract'                => $product_option_value['subtract'],
					'price'                   => $product_option_value['price'],
					'price_prefix'            => $product_option_value['price_prefix'],
					'weight'                  => $product_option_value['weight'],
					'weight_prefix'           => $product_option_value['weight_prefix']
				);
			}

			$product_option_data[] = array(
				'product_option_id'    => $product_option['product_option_id'],
				'product_option_value' => $product_option_value_data,
				'option_id'            => $product_option['option_id'],
				'name'                 => $product_option['name'],
				'type'                 => $product_option['type'],
				'value'                => $product_option['value'],
				'required'             => $product_option['required']
			);
		}

		return $product_option_data;
	}

	public function getProductDiscounts($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

		return $query->rows;
	}

	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}

	public function getProductRelated($product_id) {
		$product_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int)$product_id . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

		foreach ($query->rows as $result) {
			$product_data[$result['related_id']] = $this->getProduct($result['related_id']);
		}

		return $product_data;
	}

	public function getProductLayoutId($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");

		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return 0;
		}
	}

	public function getCategories($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

		return $query->rows;
	}

	public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
			} else {
				$sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
			}

			if (!empty($data['filter_filter'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
			} else {
				$sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
			}
		} else {
			$sql .= " FROM " . DB_PREFIX . "product p";
		}

		$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

		if (!empty($data['filter_category_id'])) {
			if (!empty($data['filter_sub_category'])) {
				$sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
			} else {
				$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
			}

			if (!empty($data['filter_filter'])) {
				$implode = array();

				$filters = explode(',', $data['filter_filter']);

				foreach ($filters as $filter_id) {
					$implode[] = (int)$filter_id;
				}

				$sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
			}
		}

		if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
			$sql .= " AND (";

			if (!empty($data['filter_name'])) {
				$implode = array();

				$words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

				foreach ($words as $word) {
					$implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
				}

				if ($implode) {
					$sql .= " " . implode(" AND ", $implode) . "";
				}

				if (!empty($data['filter_description'])) {
					$sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
				}
			}

			if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
				$sql .= " OR ";
			}

			if (!empty($data['filter_tag'])) {
				$sql .= "pd.tag LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%'";
			}

			if (!empty($data['filter_name'])) {
				$sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
				$sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
			}

			$sql .= ")";
		}

		if (!empty($data['filter_manufacturer_id'])) {
			$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getProfile($product_id, $recurring_id) {
		return $this->db->query("SELECT * FROM `" . DB_PREFIX . "recurring` `p` JOIN `" . DB_PREFIX . "product_recurring` `pp` ON `pp`.`recurring_id` = `p`.`recurring_id` AND `pp`.`product_id` = " . (int)$product_id . " WHERE `pp`.`recurring_id` = " . (int)$recurring_id . " AND `status` = 1 AND `pp`.`customer_group_id` = " . (int)$this->config->get('config_customer_group_id'))->row;
	}

	public function getProfiles($product_id) {
		return $this->db->query("SELECT `pd`.* FROM `" . DB_PREFIX . "product_recurring` `pp` JOIN `" . DB_PREFIX . "recurring_description` `pd` ON `pd`.`language_id` = " . (int)$this->config->get('config_language_id') . " AND `pd`.`recurring_id` = `pp`.`recurring_id` JOIN `" . DB_PREFIX . "recurring` `p` ON `p`.`recurring_id` = `pd`.`recurring_id` WHERE `product_id` = " . (int)$product_id . " AND `status` = 1 AND `customer_group_id` = " . (int)$this->config->get('config_customer_group_id') . " ORDER BY `sort_order` ASC")->rows;
	}

    public function addHotSearch($filter_name)
    {
        $customer_search = M('customer_search');

        $data['date_added'] = date('Y-m-d h-i-s');
        $data['search_text'] = trim($filter_name);
        $data['customer_id'] = $this->customer->getId();
        $addflag=$customer_search->add($data);
    }

    public function getHotSearch($filter_limit){
        $limit=$filter_limit;
        $filter_limit*=6;
        $sql="SELECT search_text as filter_name FROM ". DB_PREFIX . "customer_search WHERE (date_sub(curdate(), INTERVAL 30 DAY) <= date(date_added)) GROUP BY search_text  ORDER BY count(1) Desc limit ".$filter_limit;
        $data=$this->db->query($sql);
        $data=$data->rows;
        $data3=array_pad($data3,$filter_limit,'');
        $data2=array_chunk($data3,6);
        $x=0;
        $y=0;

        foreach ($data as $key ) {
            $data2[$y]['filter_name'][$x]=$key['filter_name'];
            if((++$x)=='6'){
                $x=0;
                $y++;
            }
        }
        return $data2;
    }

	public function getTotalProductSpecials() {
		$query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end >= CURDATE()))");

		if (isset($query->row['total'])) {
			return $query->row['total'];
		} else {
			return 0;
		}
	}

    public function getMaxSampleQty($product_ids) {
        $result = M('product_attribute pa')
        ->join('product_description pd on pd.product_id=pa.product_id')
        ->join('attribute a on a.attribute_id=pa.attribute_id')
        ->join('attribute_description ad on ad.attribute_id=a.attribute_id')
        ->where('pa.product_id in (' . $product_ids .") AND ad.name = '拿样数量'")
        ->field(array('pa.product_id', 'pd.name'=>'product_name', 'pa.text'=>'sample_qty'))
        ->select();
        return $result;
    }

    public function everydaySales(){
        $count = $this->everydaySold();
        return $count;
    }

    private function everydaySold(){
        $op = M('order_product');
        $p = M('product');
        $query = $op
        ->alias('op')
        ->join('LEFT JOIN `order` o ON o.order_id = op.order_id')
        ->group('op.product_id')
        ->where('o.order_status_id != 0 ')
        ->field('SUM(op.quantity) as total,op.product_id')
        ->select();
        $count = 0;
        foreach ($query as $result) {
            $where['product_id'] = $result['product_id'];
            $data['sold'] = (int)$result['total'];
            $info= $p
            ->where($where)

            ->data($data)
            ->save();
           $count+=$info;
        }

        $logger = new Log('update_sold.log');
        $logger->write($count);
        return $count;
    }
    public function getLogcenterInfoByLogcenterId($logcenter_id){
        $lg = M('logcenters');
        if(!isset($logcenter_id)){
            $logcenter_id = 0;
        }
        $where['logcenter_id'] = $logcenter_id;
        $query = $lg
        ->alias('lg')
        ->join('LEFT JOIN zone z ON lg.zone_id = z.zone_id')
        ->where($where)
        ->field('z.name')
        ->find();
        if(isset($query['name'])){
            $name = $query['name'];
        }else{
            $name = 'null';
        }
        return $name;
    }

    public function getLogcenterDisabledProductIds($customer) {
        $logcenter_id = $customer->getLogcenterId();
        $product_ids = array();
        if($logcenter_id) {
            $map['logcenter_id'] = $logcenter_id;
            $map['status'] = 0;
            $query = M('logcenter_product')->where($map)->field('product_id')->select();
            foreach ($query as $key => $value) {
                $product_ids[] = $value['product_id'];
            }
            return $product_ids;
        }
        return $product_ids;
    }

    public function getProductsStatusByOrderId($order_id){
        $order_product = M('order_product');

        $where['order_id'] = $order_id;

        $status = $order_product
        ->where($where)
        ->field('ship_status')
        ->select();
        $result = 1;
        foreach ($status as $key) {
            $result &=$key['ship_status'];
        }
        return $result;
    }

	//查找组合商品
	public function getGroupProducts($id){

		$gp = M('product')
			->alias('p')
			->join('product_description pd on pd.product_id = p.product_id','left')
			->join('product_group pg on pg.child_product_id = p.product_id','left')
			->join('product_option_value pov on pov.product_id = p.product_id','left')
			->where('pg.parent_product_id='.$id)
			->order('pg.display_order asc')
			->field('pg.child_product_id as id,pg.child_quantity as qty,pg.child_product_code as code,pd.name as name,p.sku,p.price,p.sale_price,p.is_single,p.image')
			->select();

		return $gp;

	}

	//查找特价
	public function getNowSpecial($id){

		$where['product_id'] = $id;

		$now = date("Y-m-d");

		$where['date_end'] = array('elt',$now);
		$where['date_start'] = array('elt',$now);
		$where['date_end'] = array(array('egt',$now),array('eq','0000-00-00'), 'or') ;
        $where['customer_group_id'] = (int)$this->config->get('config_customer_group_id');

		$gns = M("product_special")->where($where)->order('priority asc, price asc')->limit(1)->getField('price');

		return $gns;

	}

	//搜索列表页商品
	public function getListProduct($id,$customer){

		//检查区域
		// if($this->checkArea($id) == 0){
		// 	return;
		// }
       
		$where['p.product_id'] = $id;
        if (($customer->getIsAll())==0) {
            // $sql .= " AND p. = 0 ";
            $where['p.is_part'] = 0;

        }


		$now = date("Y-m-d");
		$where['ps.date_start'] = array('elt',$now);
		$where['ps.date_end'] = array(array('egt',$now),array('eq','0000-00-00'), 'or') ;
		$where['ps.customer_group_id'] = (int)$this->config->get('config_customer_group_id');

		$lp = M('product')
			->alias('p')
			->join('product_description pd on p.product_id = pd.product_id','left')
			->join('product_special ps on p.product_id = ps.product_id','left')
			->where($where)
			->order('ps.priority asc, ps.price asc')
			->field('p.*,pd.name as name,ps.price as special')
			->limit(1)
			->select();
            // echo M('product')->getlastsql();

		if(!$lp){
			$bwhere['p.product_id'] = $id;
            if (($customer->getIsAll())==0) {
                // $sql .= " AND p. = 0 ";
                $bwhere['p.is_part'] = 0;

            }
			$lp = M('product')
				->alias('p')
				->join('product_description pd on p.product_id = pd.product_id','left')
				->where($bwhere)
				->field('p.*,pd.name as name,p.sale_price as special')
				->select();
		}

		return $lp;

	}

    public function getclearqty($id){
         $sql = "SELECT SUM(i.available_quantity) as 'clearqty',pv.product_code FROM inventory AS i LEFT JOIN product_option_value AS pv ON pv.product_code = i.product_code LEFT JOIN warehouse AS w ON i.warehouse_id = w.warehouse_id WHERE i.`status` = 1 AND w.type!=3 AND pv.product_id=  ".$id;
                $query = $this->db->query($sql);
                $clearqty = $query->row['clearqty'];

                $procode = $pCode = substr($query->row['product_code'], 0, 10);
                $tmp = $this->getsaleqty($procode,1);
                // var_dump($tmp);
                foreach ($tmp as $wh=>$qty) {
                    $saleqty += $qty;
                }

                    // var_dump($saleqty);

                    // var_dump($clearqty);
                    // var_dump($saleqty);
                 //    die();
            $clearqty = $clearqty-$saleqty;
         $clearqty = max(0,$clearqty);

         return $clearqty;
    }

	//检查区域
	public function checkArea($id){

		$where['customer_id'] = $this->customer->getId();

		if($where['customer_id'] == 5152 || $where['customer_id'] == 3867){ //vip小胖 vip蒋总
			$show = 1;
			return $show;
		}

		$logcenter = M('customer')
			->alias('c')
			->join('logcenters l on c.logcenter_id = l.logcenter_id','left')
			->where($where)->select();
		$country_id = $logcenter[0]['country_id'];
		$zone_id = $logcenter[0]['zone_id'];

		$pta =M('product_to_area')->where('product_id='.$id)->select();
		$oc = explode(',',$pta[0]['only_countries']);
		$oz = explode(',',$pta[0]['only_zones']);
		$nc = explode(',',$pta[0]['no_countries']);
		$nz = explode(',',$pta[0]['no_zones']);

		$show = 1;
		if($pta[0]['only_countries'] != null || $pta[0]['only_zones'] != null || $pta[0]['only_zones'] != null || $pta[0]['no_zones'] != null){

			$show = 0;
			if(in_array($zone_id,$oz)){
				$show = 1;
			}
			else if(in_array($country_id,$oc) && !in_array($zone_id,$nz)){
				$show = 1;
			}
			else if(in_array($zone_id,$nz)){
				$show = 0;
			}
			else if(in_array($country_id,$nc) && !in_array($zone_id,$oz)){
				$show = 0;
			}

		}

		return $show;

	}

    //优化搜索列表页商品
    public function simplifygetListProduct($id){

        //检查区域
        if($this->checkArea($id) == 0){
            return;
        }

        $where['p.product_id'] = $id;

        $now = date("Y-m-d");
        $where['ps.date_start'] = array('elt',$now);
        $where['ps.date_end'] = array(array('egt',$now),array('eq','0000-00-00'), 'or') ;
        $where['ps.customer_group_id'] = (int)$this->config->get('config_customer_group_id');

        $lp = M('product')
            ->alias('p')
            ->join('product_description pd on p.product_id = pd.product_id','left')
            ->join('product_special ps on p.product_id = ps.product_id','left')
            ->where($where)
            ->order('ps.priority asc, ps.price asc')
            ->field('p.sale_price,p.price,ps.price as special')
            ->limit(1)
            ->select();

        if(!$lp){
            $bwhere['p.product_id'] = $id;
            $lp = M('product')
                ->alias('p')
                ->join('product_description pd on p.product_id = pd.product_id','left')
                ->where($bwhere)
                ->field('p.sale_price,p.price,p.sale_price as special')
                ->select();
        }

        return $lp;

    }

    
    public function sendcoupon($requestjson){
        $sql = "SELECT customer.customer_id, customer.is_receive FROM customer where customer.customer_id =".$requestjson['customer_id'];
        // echo $sql;
        $query = $this->db->query($sql);
        $is_receive = $query->row['is_receive'];
        // var_dump($is_receive);
        if ($is_receive==0) {
            $this->db->query("UPDATE customer SET customer.is_receive=1 where customer.customer_id =".$requestjson['customer_id']);

            $this->load->model('marketing/coupon');

            $data['name'] = '628年中红包';
            $data['type'] = 'F';
            $data['discount'] = 30;
            $data['total'] = 1000;
            $data['logged'] = 1;
            $data['shipping'] = 0;
            $data['date_start'] = '2018-06-01 00:00:00';
            $data['date_end'] = '2018-06-20 23:59:59';
            $data['uses_total'] = 1;
            $data['uses_customer'] = 1;
            $data['status'] = 1;
            $data['number'] = 1;
            $this->addCouponToCustomer($requestjson['customer_id'], $data);
            $data['name'] = '628年中红包';
            $data['type'] = 'F';
            $data['discount'] = 50;
            $data['total'] = 1500;
            $data['logged'] = 1;
            $data['shipping'] = 0;
            $data['date_start'] = '2018-06-01 00:00:00';
            $data['date_end'] = '2018-06-20 23:59:59';
            $data['uses_total'] = 1;
            $data['uses_customer'] = 1;
            $data['status'] = 1;
            $data['number'] = 1;
            $this->addCouponToCustomer($requestjson['customer_id'], $data);
            $data['name'] = '628年中红包';
            $data['type'] = 'F';
            $data['discount'] = 100;
            $data['total'] = 2500;
            $data['logged'] = 1;
            $data['shipping'] = 0;
            $data['date_start'] = '2018-06-01 00:00:00';
            $data['date_end'] = '2018-06-20 23:59:59';
            $data['uses_total'] = 1;
            $data['uses_customer'] = 1;
            $data['status'] = 1;
            $data['number'] = 1;
            $this->addCouponToCustomer($requestjson['customer_id'], $data);
            $data['name'] = '628年中红包';
            $data['type'] = 'F';
            $data['discount'] = 200;
            $data['total'] = 5000;
            $data['logged'] = 1;
            $data['shipping'] = 0;
            $data['date_start'] = '2018-06-01 00:00:00';
            $data['date_end'] = '2018-06-20 23:59:59';
            $data['uses_total'] = 1;
            $data['uses_customer'] = 1;
            $data['status'] = 1;
            $data['number'] = 1;
            $this->addCouponToCustomer($requestjson['customer_id'], $data);
            $data['name'] = '628年中红包';
            $data['type'] = 'F';
            $data['discount'] = 500;
            $data['total'] = 10000;
            $data['logged'] = 1;
            $data['shipping'] = 0;
            $data['date_start'] = '2018-06-01 00:00:00';
            $data['date_end'] = '2018-06-20 23:59:59';
            $data['uses_total'] = 1;
            $data['uses_customer'] = 1;
            $data['status'] = 1;
            $data['number'] = 1;
            $this->addCouponToCustomer($requestjson['customer_id'], $data);

        }

        return true;

    }

    public function addCouponToCustomer($customer_id, $data) {
        // echo 111;
    
        $coupon_ids = array();
        if(!isset($data['number'])) {
            $data['number'] = 1;
        }

        $number = (int)$data['number'];
        while($number > 0) {
            $number--;
            $data['code'] = substr(md5(uniqid(rand(), true)), 0, 10);
            $coupon_id = $this->addCoupon($data);
            $coupon_ids[] = $coupon_id;
        }

        foreach ($coupon_ids as $coupon_id) {
            $data2 = array('customer_id'=>$customer_id, 'coupon_id'=>$coupon_id, 'comment'=>'');
            $CustomerCoupon = M('customer_coupon');
            $CustomerCoupon->data($data2)->add();
        }
        // echo $CustomerCoupon->getlastsql();

    }

    public function addCoupon($data) {

        $this->db->query("INSERT INTO " . DB_PREFIX . "coupon SET name = '" . $this->db->escape($data['name']) . "', code = '" . $this->db->escape($data['code']) . "', discount = '" . (float)$data['discount'] . "', type = '" . $this->db->escape($data['type']) . "', total = '" . (float)$data['total'] . "', logged = '" . (int)$data['logged'] . "', shipping = '" . (int)$data['shipping'] . "', date_start = '" . $this->db->escape($data['date_start']) . "', date_end = '" . $this->db->escape($data['date_end']) . "', uses_total = '" . (int)$data['uses_total'] . "', uses_customer = '" . (int)$data['uses_customer'] . "', status = '" . (int)$data['status'] . "', date_added = NOW()");

        $coupon_id = $this->db->getLastId();

        if (isset($data['coupon_product'])) {
          foreach ($data['coupon_product'] as $product_id) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "coupon_product SET coupon_id = '" . (int)$coupon_id . "', product_id = '" . (int)$product_id . "'");
          }
        }

        if (isset($data['coupon_category'])) {
          foreach ($data['coupon_category'] as $category_id) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "coupon_category SET coupon_id = '" . (int)$coupon_id . "', category_id = '" . (int)$category_id . "'");
          }
        }


        return $coupon_id;
    }

}
